﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="AccessData.cs" company="Donga CS">
//   2011
// </copyright>
// <summary>
//   The AccessData.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DTTB.Common
{
    public class AccessData
    {
        private String[] _ParameterCollection;
        public String[] ParameterCollection
        {
            get { return _ParameterCollection; }
            set { _ParameterCollection = value; }
        }

        private object[] _ValuesCollection;
        public object[] ValuesCollection
        {
            get { return _ValuesCollection; }
            set { _ValuesCollection = value; }
        }

        private string _CommandText;
        public string CommandText
        {
            get { return _CommandText; }
            set { _CommandText = value; }
        }

        private CommandType _Commandtype;
        public CommandType Commandtype
        {
            get { return _Commandtype; }
            set { _Commandtype = value; }
        }

        private int _ErrorCode;
        public int ErrorCode
        {
            get { return _ErrorCode; }
            set { _ErrorCode = value; }
        }

        private string _ErrorMessage;
        public string ErrorMessage
        {
            get { return _ErrorMessage; }
            set { _ErrorMessage = value; }
        }

        private string _ConnectionString = ConfigurationManager.ConnectionStrings["SQLConnect"].ConnectionString;
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        private SqlDataAdapter Adapter;
        private SqlConnection Connection;
        public SqlCommand sqlCommand;
        


        /// <summary>
        /// GET ENTITY
        /// </summary>
        /// <returns>DATATABLE</returns>
        //public DataTable GetDataTable()
        //{
        //    DataTable dt = new DataTable();
        //    //try
        //    //{
        //    //    ExecuteNonQuery();//phai xem lai
        //    //    OpenConnection();
        //    //    Adapter = new SqlDataAdapter(sqlCommand);
        //    //    Adapter.Fill(dt);
        //    //    CloseConnection();
        //    //}
        //    //catch (Exception ex)
        //    //{
        //    //    ErrorMessage = ex.Message;
        //    //}
        //    ExecuteNonQuery();//phai xem lai
        //    OpenConnection();
        //    Adapter = new SqlDataAdapter(sqlCommand);
        //    Adapter.Fill(dt);
        //    CloseConnection();
        //    return dt;
        //}


        public DataTable GetDataTable1()
        {
            DataTable dt = new DataTable();

            sqlCommand = new SqlCommand();
            OpenConnection();
            sqlCommand.Connection = Connection;
            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = _Commandtype;

            if (ParameterCollection != null) //khi xử dụng paramater cho câu truy vấn
            {
                //sqlCommand.Parameters.AddRange(ParameterCollection);

                AddWithValues(sqlCommand);
            }

            Adapter = new SqlDataAdapter(sqlCommand);
            Adapter.Fill(dt);

            sqlCommand.Dispose();
            Adapter.Dispose();
            dt.Dispose();

            CloseConnection();
            return dt;
        }

        public DataTable GetDataTable()
        {
            DataTable dt = new DataTable();
            
            sqlCommand = new SqlCommand();
            OpenConnection();
            sqlCommand.Connection = Connection;
            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = _Commandtype;

            if (ParameterCollection != null) //khi xử dụng paramater cho câu truy vấn
            {
                //sqlCommand.Parameters.AddRange(ParameterCollection);

                AddWithValues(sqlCommand);
            }
            
            Adapter = new SqlDataAdapter(sqlCommand);
            Adapter.Fill(dt);

            sqlCommand.Dispose();
            Adapter.Dispose();
            dt.Dispose();

            CloseConnection();
            return dt;
        }


        /// <summary>
        /// SAVE ENTITY
        /// </summary>
        /// <returns></returns>
        public bool Save()
        {
            OpenConnection();
            //sqlCommand = new SqlCommand();
            //sqlCommand.Connection = Connection;
            //sqlCommand.CommandText = _CommandText;
            //sqlCommand.CommandType = _Commandtype;
            //Adapter=new SqlDataAdapter(sqlCommand);
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection; 
            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = _Commandtype;

            int kq = sqlCommand.ExecuteNonQuery();

            CloseConnection();

            if(kq==0)
                return false;
            return true;
        }

        public int ExecuteNonQuery()
        {
            int row = 0;
            //try
            //{
            //    OpenConnection();
            //    sqlCommand = new SqlCommand();
            //    sqlCommand.Connection = Connection;
            //    sqlCommand.CommandText = _CommandText;
            //    sqlCommand.CommandType = _Commandtype;
            //    if (ParameterCollection != null)
            //        AddWithValues(sqlCommand);
            //    row = sqlCommand.ExecuteNonQuery();
            //}
            //catch (Exception ex)
            //{
            //    ErrorMessage = ex.Message;
            //}
            //finally
            //{
            //    sqlCommand.Dispose
            //}

            OpenConnection();
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;
            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = _Commandtype;
            if (ParameterCollection != null)
                AddWithValues(sqlCommand);
            try
            {
                row = sqlCommand.ExecuteNonQuery();
            }
            catch( SqlException e)
            {
                e.StackTrace.ToString();
            }
            

            sqlCommand.Dispose();

            return row;
        }

        private void AddWithValues(SqlCommand _sqlCommand)
        {
            for (int i = 0; i < ParameterCollection.Length; i++)
                _sqlCommand.Parameters.AddWithValue(_ParameterCollection[i], _ValuesCollection[i]);
        }

        //Mở một kết nối tới DataBase
        private void OpenConnection()
        {
            Connection = new SqlConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                {
                    Connection.ConnectionString = _ConnectionString;
                    Connection.Open();
                }
            }
            catch (SqlException ex)
            {
                ErrorMessage = ex.Message;
                ErrorCode = ex.ErrorCode;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
            }
        }
        private void CloseConnection()
        {
            try
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            catch (SqlException ex)
            {
                ErrorMessage = ex.Message;
                ErrorCode = ex.ErrorCode;
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
            }
        }

        //Lưu một đối tựơng vào DataBase
        public bool Insert<T>(string tableName, T entity)
        {
            OpenConnection();

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;

            Type entityType = typeof(T);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            _CommandText = "Insert Into " + tableName + " Values(";

            for (int i = 0; i < properties.Count - 2; i++) // Bỏ qua thuộc tính ID tự tăng và property  cuối cùng
            {

                if(properties[i].PropertyType != typeof(bool))
                {
                    if (properties[i].PropertyType == typeof(System.String))
                    {
                        _CommandText += "N'" + properties[i].GetValue(entity) + "',"; // Sử dụng Unicode
                    }
                    else if (properties[i].PropertyType == typeof(System.DateTime))
                    {
                        _CommandText += "'" + ((DateTime)properties[i].GetValue(entity)).ToString("yyyy/MM/dd HH:mm:ss") + "',";
                    }
                    else
                    {
                        _CommandText += "'" + properties[i].GetValue(entity) + "',";    
                    }
                    
                }
                else
                {
                    var value = properties[i].GetValue(entity);
                    int boolValue = value != null && (Boolean)value ? 1 : 0;
                    _CommandText += boolValue + ",";
                }
               
            }
            if (properties[properties.Count - 2].PropertyType != typeof(bool)) // thuộc tính cuối cùng
            {
                if (properties[properties.Count - 2].PropertyType == typeof(System.String))
                {
                    _CommandText += "N'" + properties[properties.Count - 2].GetValue(entity) + "')"; // Sử dụng Unicode
                }
                else
                {
                    _CommandText += "'" + properties[properties.Count - 2].GetValue(entity) + "')";    
                }
                
            }
            else
            {
                if (properties.Count > properties.Count - 2)
                {
                    object value = properties[properties.Count - 2].GetValue(entity);
                    int boolValue = value != null && (Boolean)value ? 1 : 0;
                    _CommandText += boolValue + ")";
                }
            }
            //không insert thuộc tính cuối cùng  (ID) do ID tự tăng
            
            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = CommandType.Text;

            int kq = sqlCommand.ExecuteNonQuery();

            CloseConnection();

            if (kq == 0)
                return false;
            return true;
        }


        //Lưu một đối tựơng vào DataBase, trả về ID đối tượng vừa lưu
        public bool Insert<T>(string tableName, T entity, ref int idReturn)
        {
            OpenConnection();

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;

            Type entityType = typeof(T);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            _CommandText = "Insert Into " + tableName + " Values(";

            for (int i = 0; i < properties.Count - 2; i++) // Bỏ qua thuộc tính ID tự tăng và property  cuối cùng
            {

                if (properties[i].PropertyType != typeof(bool))
                {
                    if (properties[i].PropertyType == typeof(System.String))
                    {
                        _CommandText += "N'" + properties[i].GetValue(entity) + "',"; // Sử dụng Unicode
                    }
                    else if (properties[i].PropertyType == typeof(System.DateTime))
                    {
                        _CommandText += "'" + ((DateTime)properties[i].GetValue(entity)).ToString("yyyy/MM/dd HH:mm:ss") + "',";
                    }
                    else
                    {
                        _CommandText += "'" + properties[i].GetValue(entity) + "',";
                    }

                }
                else
                {
                    var value = properties[i].GetValue(entity);
                    int boolValue = value != null && (Boolean)value ? 1 : 0;
                    _CommandText += boolValue + ",";
                }

            }
            if (properties[properties.Count - 2].PropertyType != typeof(bool)) // thuộc tính cuối cùng
            {
                if (properties[properties.Count - 2].PropertyType == typeof(System.String))
                {
                    _CommandText += "N'" + properties[properties.Count - 2].GetValue(entity) + "')"; // Sử dụng Unicode
                }
                else
                {
                    _CommandText += "'" + properties[properties.Count - 2].GetValue(entity) + "')";
                }

            }
            else
            {
                if (properties.Count > properties.Count - 2)
                {
                    object value = properties[properties.Count - 2].GetValue(entity);
                    int boolValue = value != null && (Boolean)value ? 1 : 0;
                    _CommandText += boolValue + ")";
                }
            }
            //không insert thuộc tính cuối cùng  (ID) do ID tự tăng

            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = CommandType.Text;

            int kq = sqlCommand.ExecuteNonQuery();


            //Xử lý lấy ID Entity mới lưu xuống DataBase
            if (kq != 0) // Lưu thành công
            {
                //try
                //{
                    sqlCommand.CommandText = "select max(ID) from " + tableName;
                    sqlCommand.CommandType = CommandType.Text;

                    idReturn = Int32.Parse(sqlCommand.ExecuteScalar().ToString());
                //}
                //catch (Exception)
                //{

                //    idReturn = -1;
                //}
                
            }
            else
            {
                idReturn = -1;
            }

            //end

            CloseConnection();


            if(kq == 0)
                return false;
            return true;
        }


        //Update một đối tựơng vào DataBase
        public bool Update<T>(string tableName, T entity)
        {
            OpenConnection();

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;

            Type entityType = typeof(T);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            _CommandText = "UPDATE " + tableName + " SET ";


            for (int i = 0; i < properties.Count - 2; i++ )
            {
                if (properties[i].PropertyType == typeof(System.String))
                {
                    _CommandText += properties[i].Name + " = " + "N'" + properties[i].GetValue(entity) + "',";
                }
                else if (properties[i].PropertyType == typeof(System.DateTime))
                {
                    _CommandText += properties[i].Name + " = " + "'" + ((DateTime)properties[i].GetValue(entity)).ToString("yyyy/MM/dd HH:mm:ss") + "',";
                    //if (properties[i].GetValue(entity).ToString() == "1/1/0001 12:00:00 AM")
                    //{
                    //    _CommandText += properties[i].Name + " = '" +  DBNull.Value + "',";
                    //}
                    //else
                    //{
                    //    _CommandText += properties[i].Name + " = " + "'" + ((DateTime)properties[i].GetValue(entity)).ToString("yyyy/MM/dd HH:mm:ss") + "',";
                    //}
                }
                else
                {
                    _CommandText += properties[i].Name + " = '" + properties[i].GetValue(entity) + "',";
                }
                
            }
            // tách ra : mục đích bỏ dấu "," trong chuỗi truy vấn

            if (properties[properties.Count - 2].PropertyType == typeof(System.String))
            {
                _CommandText += properties[properties.Count - 2].Name + " = " + "N'" + properties[properties.Count - 2].GetValue(entity) + "'";
            }
            else
            {
                _CommandText += properties[properties.Count - 2].Name + " = '" + properties[properties.Count - 2].GetValue(entity) + "'";
            }

            

            _CommandText += " WHERE " + properties[properties.Count - 1].Name + " = " +
                            properties[properties.Count - 1].GetValue(entity); // Update trên ID _ thuộc tính cuối cùng trong Entity


            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = CommandType.Text;

            int kq = sqlCommand.ExecuteNonQuery();

            CloseConnection();

            if (kq == 0)
                return false;
            return true;
        }
        
        //Xóa luôn một đối tựong trong Database
        public bool ShiftDelete<T>(string table, string id)
        {
            OpenConnection();

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;

            Type entityType = typeof (T);

            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            _CommandText = "DELETE FROM " + table + " WHERE " + properties[properties.Count - 1].Name + " = " + id;

            //_CommandText = "DELETE " + table + " TB WHERE TB.ID = " + ID;

            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = CommandType.Text;

            int kq = sqlCommand.ExecuteNonQuery();

            CloseConnection();

            if (kq == 0)
                return false;
            return true;
        }


        public bool ShiftDelete(string sqlQuery)
        {
            OpenConnection();

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;

            _CommandText = sqlQuery;

            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = CommandType.Text;

            int kq = sqlCommand.ExecuteNonQuery();

            CloseConnection();

            if (kq == 0)
                return false;
            return true;
        }

        //Xóa một đối tượng : còn trong Database, chỉ update lại trừơng IsDelete == True
        public bool Delete<T>(string tableName, string id)
        {
            OpenConnection();

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = Connection;

            Type entityType = typeof(T);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            _CommandText = "UPDATE " + tableName + " SET IsDelete = 'True' WHERE " + properties[properties.Count - 1].Name + " = " + id;

            sqlCommand.CommandText = _CommandText;
            sqlCommand.CommandType = CommandType.Text;

            int kq = sqlCommand.ExecuteNonQuery();

            CloseConnection();

            if (kq == 0)
                return false;
            return true;
        }
    }
}